package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.SMRole;
import mwa.cs.model.SmMapping;
import mwa.cs.model.SmPackage;

public class SMMappingDAO {
private Database db;

public SMMappingDAO(Database db) {
    this.db = db;
}

//get all menu form SM_MENU 
public List<Map<String, Object>> getAllSmMenu() {
		String sql = "SELECT menu_id ,menu_name,menu_url FROM sm_menu ORDER BY menu_id";
		List<Map<String, Object>> result = db.queryList(sql);
		
		return result;
	}

//get menu where package_id and role='00' for data table 
public List<Map<String, Object>> getRoleMenu(String paPackageId) {
	String vaSql = "SELECT SM.MENU_NAME ,SM.MENU_ID,SM.MENU_URL "
				+ "FROM  SM_MENU SM "
				+ " INNER JOIN SM_MAPPING MP "
				+ "ON SM.MENU_ID = MP.MENU_ID "
				+ "AND MP.PACKAGE_ID = '"+paPackageId+"'"
				+ " AND MP.ROLE_ID= '00'";
	List<Map<String, Object>> result = db.queryList(vaSql);
	return result;
}



//get menu_id from SM_MAPPING by package
public List<Map<String, Object>> getMenuIdByPackage(String paPackageId) {
	String vaSql = "SELECT  CASE WHEN SM.MENU_ID = MP.MENU_ID THEN 'YES' ELSE 'NO' END AS CH "
			+ "FROM  SM_MENU SM  "
			+ "LEFT OUTER JOIN SM_MAPPING MP ON SM.MENU_ID = MP.MENU_ID "
			+ "AND MP.PACKAGE_ID = "+paPackageId+" "
			+ "AND MP.ROLE_ID='00' ORDER BY SM.MENU_ID ASC ";
	List<Map<String, Object>> result = db.queryList(vaSql);
	return result;
}

//get package_id from site_id 
public String getPackageIdBySiteId(String paRoleId){
	String vaSql =" select package_id "
			+ "from sites "
			+ "where site_id = ( select site_id from sm_role where role_id ='"+paRoleId+"')";
	 Map<String, Object> result = db.querySingle(vaSql);
	 if (result != null) {
         return (String) result.get("PACKAGE_ID");
     } else {
         return null;
     }
}

//get menu_id from SM_MAPPING  BY role_id 
public List<Map<String, Object>> getMenuIdByRole(String paPackageId,String paRoleId) {
	String vaSql = "  SELECT CASE WHEN COUNT(B.ROLE_ID) = 2 THEN 'YES' ELSE 'NO' END AS RESULT"
			+ " FROM (SELECT * FROM SM_MAPPING WHERE PACKAGE_ID = '"+paPackageId+"' AND ROLE_ID = '00'"
			+ " UNION ALL "
			+ "SELECT * FROM SM_MAPPING WHERE PACKAGE_ID = '"+paPackageId+"' AND ROLE_ID ='"+paRoleId+"') B  "
			+ "GROUP BY B.MENU_ID ORDER BY MENU_ID ";
	List<Map<String, Object>> result = db.queryList(vaSql);
	return result;
}


//check menu_id for insert or insert  
public List<Map<String, Object>> checkmenuId(String paPackageId ,String paMenuId ) {
	String vaSql ="SELECT MENU_ID FROM SM_MAPPING  "
			+ "WHERE MENU_ID = '"+paMenuId+"'"
			+ " AND PACKAGE_ID = "+paPackageId+""
			+ " AND ROLE_ID ='00'";
	List<Map<String, Object>> result = db.queryList(vaSql);
	
	return result;
}


//insert sm_mapping 
public void insertMenuId(SmMapping poNewSMMapping) {
	String vaSql =  "INSERT into SM_MAPPING (PACKAGE_ID,MENU_ID,ROLE_ID,"
			    + "INSERT_USER_ID,INSERT_DATE,INSERT_TIME,UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
			    + "values (?,?,?,?,?,?,?,?,?)";

 try {
		db.insert(vaSql,poNewSMMapping.getVaPackageId(),
					poNewSMMapping.getVaMenuId(),
					poNewSMMapping.getVaRoleId(),
					poNewSMMapping.getVnInsertUserId(),
					poNewSMMapping.getVaInsertDate(),
					poNewSMMapping.getVaInsertTime(),
					poNewSMMapping.getVnUpdateUserId(),
					poNewSMMapping.getVaUpdateDate(),
					poNewSMMapping.getVaUpdateTime());
		
}catch (Exception e) {
	throw new RuntimeException(e);
}
}

 //update status in sm_package 
 public void updateStatusPackage(String paPackageId,SmPackage poSmPackage) {
 	String vaSql  = " UPDATE SM_PACKAGE SET " +
 				  " STATUS = ? , "+
				  " UPDATE_USER_ID = ? , "+
				  " UPDATE_DATE = ? , " +
				  " UPDATE_TIME = ? " +
				  " WHERE " + 
				  " PACKAGE_ID= ? " ;
	 try {
			db.update(vaSql,poSmPackage.getVaStatus(),
					poSmPackage.getVnUpdateUserId(),
					poSmPackage.getVaUpdateDate(),
					poSmPackage.getVaUpdateTime(),
					paPackageId);
			
	}catch (Exception e) {
		throw new RuntimeException(e);
	}
 	
 }
 
 
 //update status in sm_role
 public void updateStatusRole(String paRoleId,SMRole poSmRole) {
	 	String vaSql  = " UPDATE SM_ROLE SET " +
	 				  " STATUS = ? , "+
					  " UPDATE_USER_ID = ? , "+
					  " UPDATE_DATE = ? , " +
					  " UPDATE_TIME = ? " +
					  " WHERE " + 
					  " ROLE_ID= ? " ;
		 try {
				db.update(vaSql,poSmRole.getVaStatus(),
						poSmRole.getVnUpdateUserId(),
						poSmRole.getVaUpdateDate(),
						poSmRole.getVaUpdateTime(),
						paRoleId);
				
		}catch (Exception e) {
			throw new RuntimeException(e);
		}
	 	
	 }
	

//delete by package_id
	public boolean deletemunuIdByPackageId(String paPackageId ){
		
    String vaSql = "DELETE FROM SM_MAPPING "
    			+" WHERE  PACKAGE_ID = '"+paPackageId+"'"
    			+" AND ROLE_ID = '00'";
    try {
        db.update(vaSql);
        db.commit();
        return true;
    } catch (Exception e) {
        return false;
    }
}
	
	//delete by role_id
	public boolean deletemunuIdByRoleId(String paPackageId,String paRoleId ){
		
	    String vaSql = "DELETE FROM SM_MAPPING "
	    			+" WHERE  PACKAGE_ID = '"+paPackageId+"'"
	    			+" AND ROLE_ID = '"+paRoleId+"'";
	    try {
	        db.update(vaSql);
	        db.commit();
	        return true;
	    } catch (Exception e) {
	        return false;
	    }
	}	

}
